package product;

import java.awt.BorderLayout;
import java.awt.EventQueue;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.sql.*;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.RowSorter.SortKey;
import javax.swing.SortOrder;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumnModel;
import javax.swing.table.TableRowSorter;
import javax.swing.JFileChooser;
import javax.swing.JInternalFrame;
import javax.swing.JOptionPane;
import javax.swing.JPopupMenu;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JMenuBar;
import javax.swing.JMenu;
import javax.swing.JMenuItem;
import javax.swing.SwingConstants;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;

import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;

import javax.swing.JLabel;

import java.awt.Font;
import java.awt.Color;
import java.io.File;

import javax.swing.JButton;
import javax.swing.JTextField;

import org.jfree.ui.RefineryUtilities;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import java.awt.FlowLayout;

public class TotalStock extends JInternalFrame {

	JPopupMenu popupMenu;
	
	private JPanel contentPane;
	private JScrollPane scrollPane;
	private JTable table;
	DefaultTableModel model;
	Connection con;
	Vector<String> col = new Vector<String>();
	String dept;
	int dept2;
	String dept1,dept3;
	String s_dept,s_dept1,s_dept2,s_dept3;
	int ptot;
	int stot;
	SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd hh:mm");
	Stock_Chart sc=null;
	private JPanel panel;
	private JTextField textField;
	private JButton btnNewButton;
	private JButton btnNewButton_1;
	DecimalFormat sd=new DecimalFormat("###,###,###");
	/**
	 * Launch the application.
	 */
/*	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					TotalStock frame = new TotalStock();
//					
					UIManager.setLookAndFeel("com.jtattoo.plaf.hifi.HiFiLookAndFeel");
					SwingUtilities.updateComponentTreeUI(frame);
			frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
*/
	/**
	 * Create the frame....
	 */
	public TotalStock()throws Exception{
		super("총 재고량 테이블", true, true, true,true);
		setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
		setBounds(100, 100, 750, 374);
		contentPane = new JPanel();
		contentPane.setOpaque(false);
		contentPane.setBackground(new Color(0, 153, 255));
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		col.add("제품명");
		col.add("제품코드");
		col.add("재고량");
		col.add("업데이트일자");		
		model = new DefaultTableModel(col, 0){
			public boolean isCellEditable(int r, int c){ // 테이블 편집불가능하게 한다. 
				  return false ;
			 }
		};
		contentPane.add(getScrollPane());
		contentPane.add(getPanel());
		contentPane.add(getBtnNewButton_1());
		conn();
		DefaultTableCellRenderer Center = new DefaultTableCellRenderer();
		Center.setHorizontalAlignment(SwingConstants.CENTER); // 가운데 정렬
		DefaultTableCellRenderer Right = new DefaultTableCellRenderer();
		Right.setHorizontalAlignment(SwingConstants.RIGHT);// 우측 정렬
		TableColumnModel tcmSchedule = table.getColumnModel();
		tcmSchedule.getColumn(0).setCellRenderer(Center);
		tcmSchedule.getColumn(1).setCellRenderer(Right);
		tcmSchedule.getColumn(2).setCellRenderer(Center);
	}
	void DbCon()throws Exception{
		 Class.forName("oracle.jdbc.driver.OracleDriver");
		 System.out.println("드라이버 로딩 성공...");
	      String url="jdbc:oracle:thin:@192.168.10.4:1521:XE";
	        
	      String user="hr";
	      String pwd="hr";
	        
	     con=DriverManager.getConnection(url,user,pwd);
	      System.out.println("DB 연결 성공!");
	}
	public void conn()throws Exception{

		DbCon();

	      Statement st=con.createStatement();
	     
	      String sql="select c.pro_nm,s.* from stock s,code c where c.pro_code=s.s_code";
	      ResultSet rs=st.executeQuery(sql);

	      while(rs.next()){
	    	  Vector<String> v=new Vector<String>();
	    	   dept=rs.getString(1);
	          dept1=rs.getString(2);
	          dept2=Integer.parseInt(rs.getString(3));
	          dept3=rs.getString(4);
	          v.add(dept);
	          v.add(dept1);
	          v.add(sd.format(dept2));
	          v.add(dept3);
	          model.addRow(v);
	      }
	      int cnt=0;

	      rs.close();
	      st.close();
	      con.close();
	}
	public void conn2()throws Exception{
		String sql="";
	
		DbCon();
		model.setRowCount(0);
		String find="%"+textField.getText()+"%";
	      Statement st=con.createStatement();
	      if(find.equals("")){
	    	  sql="select c.pro_nm,s.* from stock s,code c where c.pro_code=s.s_code";
	      }else{
	      sql="select c.pro_nm,s.* from stock s,code c where c.pro_code=s.s_code and (s_code like '"+find+"'"
	      		+ " or pro_nm like '"+find+"')";
	      }
	      
	      ResultSet rs=st.executeQuery(sql);
	  
	      while(rs.next()){
	    	  
	    	  Vector<String> v=new Vector<String>();
	    	   dept=rs.getString(1);
	          dept1=rs.getString(2);
	          dept2=Integer.parseInt(rs.getString(3));
	          dept3=rs.getString(4);
	          v.add(dept);
	          v.add(dept1);
	          v.add(sd.format(dept2));
	          v.add(dept3);
	          model.addRow(v);
	      }
	      int cnt=0;

	      rs.close();
	      st.close();
	      con.close();
	}
	private JScrollPane getScrollPane(){
		if (scrollPane == null) {
			scrollPane = new JScrollPane();
			scrollPane.setOpaque(false);
			scrollPane.setBackground(new Color(51, 153, 255));
			scrollPane.setBounds(0, 36, 750, 268);
			scrollPane.setViewportView(getTable());
		}
		return scrollPane;
	}
	private JTable getTable(){
		if(table == null){
			table = new JTable(model);
			table.addMouseListener(new MouseAdapter() {
				@Override
				public void mousePressed(MouseEvent e) {
					if(SwingUtilities.isRightMouseButton(e)){ // 마우스 우클릭 //이것도 클릭과 프레스만 하면 우클릭 시 바로 뜸
						popupMenu.show(e.getComponent(), e.getX(), e.getY());
					}
				}
			});
			table.setAutoCreateRowSorter(true);
			TableRowSorter tableSorter = new TableRowSorter(table.getModel());
			table.setRowSorter(tableSorter);
		}
		popupMenu = new JPopupMenu();
		getContentPane().add(popupMenu);
		itemAdd("선택된 제품별 파이 그래프(생산, 판매, 재고량)",popupMenu);
		itemAdd("선택된 제품별 파이 그래프(날짜별 재고량)",popupMenu);
		popupMenu.addSeparator();
		itemAdd("선택된 제품들의 총재고 중 각 제품들의 비율",popupMenu);
		itemAdd("선택된 총재고 파이 그래프(생산, 판매, 재고량)",popupMenu);
		return table;
	}
	
	private void itemAdd(final String string, JPopupMenu pMenu2) {
		JMenuItem mi = new JMenuItem(string);
		mi.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				
				int graphCount = table.getSelectedRowCount(); // 만들어야 하는 그래프의 숫자
				int ColumnCountForFor = table.getColumnCount(); // 포문을 돌리기 위한 칼럼의 숫자
				int[] rowsToGraph = table.getSelectedRows(); // 그래프로 바꿀 테이블 행의 모음
				int writeLine = 0;
				
				switch(string) {
				case "선택된 제품별 파이 그래프(생산, 판매, 재고량)": // 멀티 파이 그래프

					try {

					DbCon();
						
					String[][] codeProductSale = new String[graphCount][3];
						
					String[] codeList = new String[graphCount];

					for(int i : rowsToGraph) { //그래프로 만들 행의 모음을 출력함
//						System.out.println("i = "+i);
							codeList[writeLine]=(String) table.getValueAt(i, 1);
						writeLine++; //배열의 다음 행에 집어넣음
					}
					
//					for(String i : codeList) System.out.println(i);

					writeLine = 0;
					
					String sql = "select distinct(code), sum(ea) from product where "; //생산 구하기

					for (int i = 0; i < graphCount; i++) {
						sql += "code=?";
						if (i != graphCount-1) sql += " or ";
					}

					sql += " group by code order by code";

					PreparedStatement pstmt = con.prepareStatement(sql);
						for (int i = 0; i < graphCount; i++) { // 문제점 ?는 1부터 시작, 배열은 0부터 시작
							pstmt.setString(i+1, codeList[i]); //?가 뭔지 알려줌
						}
						
						ResultSet rs = pstmt.executeQuery();
						while(rs.next()) {
							codeProductSale[writeLine][0]=rs.getString("code");
							codeProductSale[writeLine][1]=rs.getInt("sum(ea)")+"";
							writeLine++;
						}
						
						writeLine = 0;
						String sql2 = "select distinct(sa_pro_code), sum(sa_ea) from sale where ";  //판매 구하기

						for (int i = 0; i < graphCount; i++) {
							sql2 += "sa_pro_code=?";
							if (i != graphCount-1) sql2 += " or ";
						}

						sql2 += " group by sa_pro_code order by sa_pro_code";
								
						pstmt = con.prepareStatement(sql2);
						for (int i = 0; i < graphCount; i++) { // 문제점 ?는 1부터 시작, 배열은 0부터 시작
							pstmt.setString(i+1, codeList[i]); //?가 뭔지 알려줌
						}
						rs = pstmt.executeQuery();
//						System.out.println("---------멀정?");
						while(rs.next()) {
//							System.out.println("---------멀정??");
//							System.out.println(rs.getInt("sum(sa_ea)") + " 공백확인");
/*							if (rs.getString("sum(sa_ea)").equals("")) codeProductSale[writeLine][2]=0+"";
							else codeProductSale[writeLine][2]=rs.getInt("sum(sa_ea)")+"";*/
							codeProductSale[writeLine][2]=rs.getInt("sum(sa_ea)")+"";
							writeLine++;
						}
/*						System.out.println("확인");
						System.out.println(codeList.length);
						System.out.println(codeList[0].length());
						for (int i = 0; i < codeProductSale.length; i++) {
							for (int j = 0; j < codeProductSale[0].length; j++) {
								System.out.println(codeProductSale[i][j]);
							}
							System.out.println("--------");
						}*/
						
						pieChart(codeProductSale);
						
						rs.close();
						pstmt.close();
						con.close();
					} catch (Exception e2) {
						e2.printStackTrace();
					}
					break;

				case "선택된 제품별 파이 그래프(날짜별 재고량)":
/*					select distinct(nal), sum(ea) from product group by nal
					select distinct(sa_reg_date), sum(sa_ea) from sale group by sa_reg_date
					
					*재고량 연산하려면 처음부터 해당날짜까지 다 해야됨->기간이면? 1월 1일~1월 5일까지면? 5번 연산을 해야됨 
							->제대로 계산하려면 10번
							also if 5월 1~5월 5일까지 계산하려면 1~5월1일까지 + (1~5월1일까지+5월2일)...... 이런 식이 됨
*/
					try {
						DbCon();
						
						ArrayList totalProductList = new ArrayList();
						
						String[] codeList = new String[graphCount];

						for(int i : rowsToGraph) { //그래프로 만들 행의 모음을 출력함
								codeList[writeLine]=(String) table.getValueAt(i, 1);
							writeLine++; //배열의 다음 행에 집어넣음
						}
						
//						for(String i : codeList) System.out.println(i);

						writeLine = 0;
						
//						select count(distinct(to_char(nal, 'yyyy-mm-dd'))) from product

//						select count(distinct(to_char(sa_reg_date, 'yyyy-mm-dd'))) from sale

						//이름, 날짜, 갯수 = 3차원 배열이어야 함
						//날짜, 갯수 = 2차원 배열 (날짜 수)*2
						
						for (int i = 0; i < graphCount; i++) { // 제품별 생산

							String sql = "select count(distinct(to_char(nal, 'yyyy-mm-dd'))) as nalCount from product where code=?";
							
							PreparedStatement pstmt = con.prepareStatement(sql); //클래스 선언한 횟수만큼만 닫아주면 된다. 변수재사용은 닫는 횟수랑 무관
							
							pstmt.setString(1, codeList[i]); //?가 뭔지 알려줌
							
//							System.out.println(codeList[i]);
							
//							System.out.println(sql);
							
							ResultSet rs = pstmt.executeQuery();
							
							int nalCount = 0;
							
							if (rs.next()) { // next는 반드시 호출되야함
								nalCount = rs.getInt("nalCount");
//								nalCount = rs.getInt("count(distinct(to_char(nal, 'yyyy-mm-dd')))"); // 이렇게 하면 에러 뜸
//								System.out.println(nalCount); // 한번 찍힘
							}

							String[][] nalAndAmount = new String[nalCount][2];

							sql = "select distinct(to_char(nal, 'yyyy-mm-dd')) as proNal, sum(ea) "
									+ "from product where code=?"
									+ " group by to_char(nal, 'yyyy-mm-dd')"
									+ " order by to_char(nal, 'yyyy-mm-dd')";
							
							pstmt = con.prepareStatement(sql);
							
							pstmt.setString(1, codeList[i]); //?가 뭔지 알려줌
							
							rs = pstmt.executeQuery();
							
							while (rs.next()) {
/*								for (int j = 0; j < nalAndAmount.length; j++) {
//									nalAndAmount[j][0]=rs.getString("distinct(nal)");
									nalAndAmount[j][0]=rs.getString("proNal");
									nalAndAmount[j][1]=rs.getInt("sum(ea)")+"";*/
//								System.out.println(rs.getString("proNal"));
//								System.out.println(rs.getInt("sum(ea)"));
								
								nalAndAmount[writeLine][0]=rs.getString("proNal");
								nalAndAmount[writeLine][1]=rs.getInt("sum(ea)")+"";
								
//								System.out.println(nalAndAmount[writeLine][0] + " : " +nalAndAmount[writeLine][1]); //정상

								writeLine++;
//								}
							}
/*							for (int j = 0; j < nalAndAmount.length; j++) { //확인
								for (int k = 0; k < 2; k++) {
									System.out.println(nalAndAmount[j][k]);
								}
							}*/

//							System.out.println("--------------------");
							
							totalProductList.add(nalAndAmount);
							
							writeLine = 0;
//							System.out.println(totalList.get(0)); //외계어=정상
/*							String[][] ext = (String[][]) totalList.get(0); //확인 정상
							for (int j = 0; j < ext.length; j++) {
								for (int k = 0; k < 2; k++) {
								System.out.println(ext[j][k]);
								}
							}*/
							rs.close();
							pstmt.close();
							
						}
						
						System.out.println("생산 계산 후 사이즈"+totalProductList.size());

/*						System.out.println(totalList.size());
						String[][] ext;
						
						for (int j = 0; j < totalList.size(); j++) {
							ext = (String[][]) totalList.get(j); //확인 정상
							for (int j2 = 0; j2 < ext.length; j2++) {
								for (int k = 0; k < 2; k++) {
								System.out.println("ext : " + ext[j2][k]);
								}
							}
							System.out.println("------------------------");
						}
*/
						ArrayList totalSaleList = new ArrayList();
						
						for (int i = 0; i < graphCount; i++) { //제품별 판매

							String sql = "select count(distinct(to_char(sa_reg_date, 'yyyy-mm-dd'))) as sa_reg_dateCount from sale where sa_pro_code=?";
							
							PreparedStatement pstmt = con.prepareStatement(sql);
							
							pstmt.setString(1, codeList[i]); //?가 뭔지 알려줌
							
//							System.out.println(sql);
							
							ResultSet rs = pstmt.executeQuery();
							
							int sa_pro_dateCount = 0;
							
							if (rs.next()) { // next는 반드시 호출되야함
								sa_pro_dateCount = rs.getInt("sa_reg_dateCount");
//								System.out.println(nalCount); // 한번 찍힘
							}
							
							String[][] sa_pro_dateAndSa_ea = null;
							
							if (sa_pro_dateCount==0) {
								sa_pro_dateAndSa_ea = new String[1][2];
							}
							else {

							sa_pro_dateAndSa_ea = new String[sa_pro_dateCount][2];

							sql = "select distinct(to_char(sa_reg_date, 'yyyy-mm-dd')) as saleDate, sum(sa_ea)"
									+ " from sale where sa_pro_code=?"
									+ " group by to_char(sa_reg_date, 'yyyy-mm-dd')"
									+ " order by to_char(sa_reg_date, 'yyyy-mm-dd')";
							
							pstmt = con.prepareStatement(sql);
							
							pstmt.setString(1, codeList[i]); //?가 뭔지 알려줌
							
							rs = pstmt.executeQuery();
							
							while (rs.next()) {
//								System.out.println(rs.getString("proNal"));
//								System.out.println(rs.getInt("sum(ea)"));
								
								sa_pro_dateAndSa_ea[writeLine][0]=rs.getString("saleDate");
								sa_pro_dateAndSa_ea[writeLine][1]=-rs.getInt("sum(sa_ea)")+"";
								
//								System.out.println(sa_pro_dateAndSa_ea[writeLine][0] + " : " +sa_pro_dateAndSa_ea[writeLine][1]); //정상

								writeLine++;
//								}
							}
/*							for (int j = 0; j < sa_pro_dateAndSa_ea.length; j++) { //확인
								for (int k = 0; k < 2; k++) {
									System.out.println(sa_pro_dateAndSa_ea[j][k]);
								}
							}

							System.out.println("--------------------");*/
							}
							
//							totalSaleList.add(sa_pro_dateAndSa_ea);
							totalProductList.add(sa_pro_dateAndSa_ea);
							
							writeLine = 0;

							rs.close();
							pstmt.close();
						}
						
/*						System.out.println("판매 계산 후 사이즈"+totalList.size());
						String[][] ext;
						for (int j = 0; j < totalList.size(); j++) {
							ext = (String[][]) totalList.get(j); //확인 정상
							for (int j2 = 0; j2 < ext.length; j2++) {
								for (int k = 0; k < 2; k++) {
								System.out.println("ext : " + ext[j2][k]);
								}
							}
							System.out.println("------------------------");
						}
*/						
//							pieChart2(codeList, totalProductList, totalSaleList); // 생산과 판매 분리해야하나?
							pieChart2(codeList, totalProductList); // 생산과 판매 분리해야하나?
							con.close();
						
					} catch (Exception e2) {
						e2.printStackTrace();
					}
					break;

				case "선택된 제품들의 총재고 중 각 제품들의 비율": // 단일 파이 그래프 - 테이블에서 긁으면 됨, 제품별로 재고량 입력

					String[][] temp = new String[graphCount][ColumnCountForFor-1];
					
//					System.out.println("1");
										
					for(int i : rowsToGraph) { //그래프로 만들 행의 모음을 출력함
//						System.out.println("2");
						for (int j = 0; j < ColumnCountForFor-1; j++) { //칼럼의 숫자만큼 포문을 돌려서
//							System.out.println(temp[writeLine][j]);
//							System.out.println("3");
							if (j==1) {
//								System.out.println("4");
							String after2 = ""; // 컴마가 포함된 경우 제거해서 저장하는 소스 포함
								String[] after = ((String) table.getValueAt(i, 2)).split(",", 4);
								for(String s: after) after2+=s;
								temp[writeLine][1]=after2;
							}
							else temp[writeLine][j]=(String) table.getValueAt(i, j+1); //2차원 배열에 0,0부터 집어넣음
//							System.out.println(temp[writeLine][j]);
//							System.out.println("5");
						}
						writeLine++; //배열의 다음 행에 집어넣음
					}

					pieChart3(temp);
					break;
					
				case "선택된 총재고 파이 그래프(생산, 판매, 재고량)": // 단일 파이 그래프 - 테이블에서 긁을 수 없음 -> 쿼리 써야 됨
					
					try {
						
						DbCon();
						
//						String[][] codeProductSale = new String[graphCount][3]; // 이것 때문에 널 포인트 떴었음 ㅡ.,ㅡ;
						
						String[][] codeProductSale = new String[1][3]; //0으로 하지 말자 ㅡ.ㅡ;;;
							
						String[] codeList = new String[graphCount];
						
						String codeName = ""; // 코드명 저장한 문자열

						for(int i : rowsToGraph) { //그래프로 만들 행의 모음을 출력함
							System.out.println("i = "+i);
								codeList[writeLine]=(String) table.getValueAt(i, 1);
							writeLine++; //배열의 다음 행에 집어넣음
						}
						
//						for(String i : codeList) System.out.println(i);

						String sql = "select sum(ea) from product where ";

						for (int i = 0; i < graphCount; i++) {
							sql += "code=?";
							if (i != graphCount-1) sql += " or ";
						}
						
						PreparedStatement pstmt = con.prepareStatement(sql);
						for (int i = 0; i < graphCount; i++) { // 문제점 ?는 1부터 시작, 배열은 0부터 시작
							pstmt.setString(i+1, codeList[i]); //?가 뭔지 알려줌
							

							if (i==0) codeName += codeList[i]; // 코드명 더하기
							else codeName += ", " + codeList[i];
						}
						
						codeProductSale[0][0]=codeName;
						
						ResultSet rs = pstmt.executeQuery();
						if (rs.next()) {
							codeProductSale[0][1]=rs.getInt("sum(ea)")+"";
						}

						String sql2 = "select sum(sa_ea) from sale where ";

						for (int i = 0; i < graphCount; i++) {
							sql2 += "sa_pro_code=?";
							if (i != graphCount-1) sql2 += " or ";
						}

						pstmt = con.prepareStatement(sql2);
						for (int i = 0; i < graphCount; i++) { // 문제점 ?는 1부터 시작, 배열은 0부터 시작
							pstmt.setString(i+1, codeList[i]); //?가 뭔지 알려줌
						}
						rs = pstmt.executeQuery();
						System.out.println("---------멀정?");
						if (rs.next()) {
							codeProductSale[0][2]=rs.getInt("sum(sa_ea)")+"";
						}
						
//						System.out.println(codeProductSale[0][0]);
						
						System.out.println(codeProductSale.length);
						
						pieChart(codeProductSale);
						
						break;
					} catch (Exception e2) {
						e2.printStackTrace();
					}

				}				
			}
		});
		pMenu2.add(mi); 
	}
	
	void pieChart(String[][] codeProductSale) {
		MultiPieChart chart = new MultiPieChart(codeProductSale);
		chart.pack();
        RefineryUtilities.centerFrameOnScreen(chart);
		chart.setVisible(true);
	}
	
//	void pieChart2(String[] codeList, ArrayList totalProductList, ArrayList totalSaleList) {
	void pieChart2(String[] codeList, ArrayList totalProductList) {		
//		MultiPieChart chart = new MultiPieChart(codeList, totalProductList, totalSaleList);
		MultiPieChart chart = new MultiPieChart(codeList, totalProductList);
		chart.pack();
        RefineryUtilities.centerFrameOnScreen(chart);
		chart.setVisible(true);
	}
	
	void pieChart3(String[][] temp) {
		SinglePieChart3D chart3 = new SinglePieChart3D(temp);
		chart3.pack();
        RefineryUtilities.centerFrameOnScreen(chart3);
		chart3.setVisible(true);
	}
	
	private JPanel getPanel() {
		if (panel == null) {
			panel = new JPanel();
			panel.setBounds(140, 0, 451, 37);
			panel.setLayout(new FlowLayout(FlowLayout.CENTER, 5, 5));
			panel.add(getTextField());
			panel.add(getBtnNewButton());
		}
		return panel;
	}
	private JTextField getTextField() {
		if (textField == null) {
			textField = new JTextField();
			textField.setColumns(10);
		}
		return textField;
	}
	private JButton getBtnNewButton() {
		if (btnNewButton == null) {
			btnNewButton = new JButton("검색");
			btnNewButton.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					try {
						conn2();
					} catch (Exception e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
				}
			});
		}
		return btnNewButton;
	}
	public void ExcelSave(JTable table) {// 엑셀 저장하는 메소드(매개변수 JTable)
		try {
			JFileChooser jfc = new JFileChooser();
			int yn = jfc.showSaveDialog(null);
			if (yn == 0) {
				String fileName = jfc.getSelectedFile().toString();// 저장경로 받기
				WritableWorkbook workbook = Workbook.createWorkbook(new File(
						fileName + ".xls"));

				WritableSheet s1 = workbook.createSheet("sheet 0", 0); // 엑셀에
																		// 시트생성
				// WritableSheet s2 = workbook.createSheet("sheet 1", 1); //엑셀에
				// 시트생성

				for (int i = 0; i < table.getColumnCount(); i++) {// 열 제목 추가
					Label label = new Label(i, 0, table.getColumnName(i));
					s1.addCell(label);
				}

				// 데이터 저장
				for (int i = 0; i < table.getRowCount(); i++) {// 행 추가
					for (int j = 0; j < table.getColumnCount(); j++) {
						Label label = new Label(j, i + 1,
								(String) table.getValueAt(i, j)); // 라벨객체 생성.
						s1.addCell(label);
					}
				}
				JOptionPane.showMessageDialog(null, fileName + ".xls 에 저장되었습니다");// 알림창
				workbook.write(); // 파일로 쓰기
				workbook.close(); // 닫기
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	private JButton getBtnNewButton_1() {
		if (btnNewButton_1 == null) {
			btnNewButton_1 = new JButton("엑셀로저장");
			btnNewButton_1.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					ExcelSave(table);
				}
			});
			btnNewButton_1.setBounds(354, 312, 97, 23);
		}
		return btnNewButton_1;
	}
}
